USE [NguyenHoa]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/*********************** tblProduct ***********************/

-- Drop the dbo.tblProduct_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_Insert
(

	@Id int    OUTPUT,

	@Code varchar (128)  ,

	@Name nvarchar (128)  ,

	@AlbumId int   ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  ,

	@ModifiedDate datetime   ,

	@ModifiedBy varchar (50)  
)
AS
	
	DECLARE @MaxId INT
	DECLARE @Temp varchar(6)

	SELECT @MaxId = ISNULL(Max(Id),0) from tblProduct

  SET @Temp = @MaxId + 1
  SET @Code = REPLICATE('0', 6 - LEN(@Temp)) + @Temp

  SELECT @Code = 'PRO-' + @Code

	INSERT INTO [dbo].[tblProduct]
		(
		[Code]
		,[Name]
		,[AlbumId]
		,[CreatedDate]
		,[CreatedBy]
		,[ModifiedDate]
		,[ModifiedBy]
		)
	VALUES
		(
		@Code
		,@Name
		,@AlbumId
		,@CreatedDate
		,@CreatedBy
		,@ModifiedDate
		,@ModifiedBy
		)
	
	-- Get the identity value
	SET @Id = SCOPE_IDENTITY()
									
GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO